<?php



/**

 * @version		$Id: hello.php 15 2009-11-02 18:37:15Z chdemko $

 * @package		Joomla16.Tutorials

 * @subpackage	Components

 * @copyright	Copyright (C) 2005 - 2010 Open Source Matters, Inc. All rights reserved.

 * @author		Christophe Demko

 * @link		http://joomlacode.org/gf/project/helloworld_1_6/

 * @license		License GNU General Public License version 2 or later

 */



// No direct access to this file

defined('_JEXEC') or die('Restricted access');



// import Joomla modelitem library

jimport('joomla.application.component.modelitem');



/**

 * HelloWorld Model

 */

class bgModelbg extends JModel

{

    var $_total = null;

    var $_pagination = null;

    

    public function __construct(){

    	parent::__construct();

 

        $mainframe = JFactory::getApplication();

 

        // Get pagination request variables

        $limit = $mainframe->getUserStateFromRequest('com_information.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');

        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');

 

        // In case limit has been changed, adjust it

        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);

 

        $this->setState('limit', $limit);

        $this->setState('limitstart', $limitstart);

    }

	function getAll($list = null){



	    $qr = "SELECT INFO_CODE,INFO_CAIN_CODE,INFO_TITLE,INFO_DATE,INFO_FLAG,CAIN_NAME,INFO_FILE_1,INFO_FILE_2,INFO_FILE_3,INFO_FILE_4,INFO_FILE_5

                FROM info 

                INNER JOIN cain ON INFO_CAIN_CODE=CAIN_CODE";

       $where = array();

       // search

       

       if(isset($list['filter_search'])){

            $where[] = "INFO_TITLE LIKE '%$list[filter_search]%'";

       }

      

       if($where){

            $where = ' WHERE '.implode(' AND ', $where);

        }else{

            $where = null;

        }

       // get total number record

       $this->_total = $this->getTotal($where);

       

       $qr .= $where;

       

       // order by

       $qr .= " ORDER BY INFO_DATE DESC"; 

       

       // pagination

       $qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

           

        

       // load data

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       

       return $result;

	}



    function getTotal($where = null){

        // Load the content if it doesn't already exist

        if ($this->_total === null) {

            $query = "SELECT count(*) FROM info 

                        INNER JOIN cain ON INFO_CAIN_CODE=CAIN_CODE $where";

            $this->_db->setQuery($query);

            $this->_total = $this->_db->loadResult();

        }

        

        return $this->_total;

    } 

    

    function getPagination(){

        // Load the content if it doesn't already exist

        if (empty($this->_pagination)) {

            jimport('joomla.html.pagination');

            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );

        }

        return $this->_pagination;

    }

    

      public function remove($cid){

        $cid = implode(',', $cid);

        

        $qrdeluser = "DELETE FROM usin WHERE USIN_INFO_CODE IN($cid)";

        $this->_db->setQuery($qrdeluser);

        $this->_db->query();

    	$query = "DELETE FROM info WHERE INFO_CODE IN($cid)";

        $this->_db->setQuery($query);

        if($this->_db->query()){

            return true;

        }else{

            return false;

        }

    }

    

    

        public function save($info_title,$info_body,$info_cain_code,$infodate,$info_flag,$list_user,$file1,$file2,$file3,$file4,$file5,$addnew,$cid)

        {

            if ($addnew==1)

            {

                    $query = "insert into info(info_title,info_body,info_cain_code,info_date,info_flag,info_file_1,

                                            info_file_2,info_file_3,info_file_4,info_file_5) values('$info_title',

                                            '$info_body',$info_cain_code,'$infodate',$info_flag,'$file1','$file2',

                                            '$file3','$file4','$file5')";

            

              

                    $this->_db->setQuery($query);

                    $this->_db->query();

              

                        

                    $query = "SELECT max(INFO_CODE) FROM info";

                    $this->_db->setQuery($query);

                    $this->_total = $this->_db->loadResult();

                    $ls=$this->_total;

                     $array= (explode(";",$list_user));  

                     array_pop($array);                           

                     foreach ($array as $key => $value)

                     {

                        $str = substr($value, strripos($value,'(', 1)+1);
                        $str = str_replace(')', '', $str);
                        $query = "insert into usin values($ls,$str)";

                        $this->_db->setQuery($query);

                        $this->_db->query();

                        }

            }

            //edit record 

            else if ($addnew==0)

            {

               

                    $query = "update info set info_title='$info_title',info_body='$info_body',INFO_CAIN_CODE=$info_cain_code,

                                              info_date='$infodate',info_flag=$info_flag,info_file_1='$file1',info_file_2='$file2',

                                              info_file_3='$file3',info_file_4='$file4',info_file_5='$file5'

                                              where  info_code=$cid";

                                             

                    $this->_db->setQuery($query);

                    $this->_db->query();

                    

                    

                    $qedelete="delete from usin where USIN_INFO_CODE=$cid";

                    $this->_db->setQuery($qedelete);

                    $this->_db->query();

                    

                    

                     $array= (explode(";",$list_user));   

                     array_pop($array); 

                              

                     foreach ($array as $key => $value)

                     {

                        $str = strstr($value,"(");

                        $str =substr($str,1);

                        $str=substr($str,0,-1);

                        settype($str, "int");

                        $query = "insert into usin values($cid,$str)";

                        $this->_db->setQuery($query);

                        $this->_db->query();

                     

                        }

                    

                    }

                    

           

        }

    



        public function savetemp($data, $addNew = false){

        	$table = & JTable::getInstance('bg','bgTable');

            $table->bind($data);

            

            if($table->store(false, $addNew)){

                return true;

            }

            

            return false;

        }

    

    

     function getAllcain(){

	   $qr = "SELECT CAIN_CODE,CAIN_NAME FROM cain";

       // order by

       $qr .= " ORDER BY CAIN_CODE DESC"; 

       // load data

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       return $result;

	}

    

    /**
     * GET LIST FILE_NAME FROM INFO_CODE 
    **/

    public function getFileNameFromINFO_CODE($cid){

        $qr = "SELECT INFO_FILE_1,INFO_FILE_2,INFO_FILE_3,INFO_FILE_4,INFO_FILE_5

                 FROM info WHERE INFO_CODE = $cid";

       // load data

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObject();

       

       return $result;

    }

    

    

    function getAllUser($list = null){



	    $qr = "SELECT id,name,username,email from #__users";

     

       $qr .= " ORDER BY id DESC"; 



        

       // load data

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       

       return $result;

	}

    

    

   function getAllListUser($list = null){

    

	   $qr = "SELECT * from #__users";

       // search

       if(!empty($list['USERNAME'])){

            $where = " WHERE username LIKE '%$list[USERNAME]%' OR id = '$list[USERNAME]'";

       }else{

            $where = null;

       }

       

       // get total number record

       //$this->_total = $this->getTotalTown($where);

       

       $qr .= $where;

       

       // order by

       $qr .= " ORDER BY id DESC"; 

       

       // pagination

       //$qr .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

          

       

       // load data

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       

       return $result;

	}

    

    

    function getTotalUser($where = null){

        // Load the content if it doesn't already exist

        if ($this->_total === null) {

            $query = "SELECT count(*) FROM #__users $where";

            $this->_db->setQuery($query);

            $this->_total = $this->_db->loadResult();

        }

        

        return $this->_total;

    } 

    

    function getPaginationUser(){

        // Load the content if it doesn't already exist

        if (empty($this->_pagination)) {

            jimport('joomla.html.pagination');

            $this->_pagination = new JPagination($this->getTotalUser(), $this->getState('limitstart'), $this->getState('limit') );

        }

        return $this->_pagination;

    }

    

    

     function load($cid){

        

        $query = "SELECT INFO_CODE,INFO_TITLE,INFO_BODY,INFO_CAIN_CODE,USIN_USER_CODE,USERNAME,INFO_FLAG,

                                INFO_FILE_1,INFO_FILE_2,INFO_FILE_3,INFO_FILE_4,INFO_FILE_5

                         FROM   info left join usin on INFO_CODE=USIN_INFO_CODE 

                                left join kc2vr_users  on kc2vr_users.id=USIN_USER_CODE

                         WHERE  INFO_CODE=($cid)";

                         

        //echo $query;

        $this->_db->setQuery($query);

        $result = $this->_db->loadObject();

       // echo ($result->info_body);

        return $result;

        

    }

    function loadlistuser($cid){

      

        $query = "SELECT USIN_USER_CODE,name FROM usin,#__users 

                         WHERE  #__users.id=USIN_USER_CODE AND USIN_INFO_CODE=($cid)";

        $this->_db->setQuery($query);

        $result = $this->_db->loadObjectList();

       // echo ($result->info_body);

        return $result;

        

    } 

    

    //

    function getAllCainPopup($list = null){



	   $qr = "SELECT CAIN_CODE,CAIN_NAME FROM cain";

       // order by

       $qr .= " ORDER BY CAIN_CODE DESC"; 

              

       // load data

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       

       return $result;

	}     

      //Get CAIN_CODE max

    function getMaxCain_code($where = null){

        // Load the content if it doesn't already exist

        $query = "SELECT MAX(CAIN_CODE) FROM cain";

            

        $this->_db->setQuery($query);

        $this->_max = $this->_db->loadResult();

        

        return $this->_max;

    }

    

     //get cadm from cadm_code

    public function getCainFromID($cid){

        $qr = "SELECT CAIN_CODE,CAIN_NAME FROM cain WHERE CAIN_CODE = $cid";

       // load data

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObject();

       

       return $result;

    }

    

     //save category information

    public function savecain($data,$addNew){

        if($addNew){

            $sql = "INSERT INTO cain (CAIN_CODE, CAIN_NAME) 

                VALUES ($data[CAIN_CODE], '$data[CAIN_NAME]')";

        }else{

            $sql = "UPDATE cain 

                    SET CAIN_NAME = '$data[CAIN_NAME]' WHERE CAIN_CODE = $data[CAIN_CODE]";

        }

    	

        $this->_db->setQuery($sql);       

        if($this->_db->query()){

            return true;

        }else{

            return false;

        }

    }

    

     //delete cain

    public function removeCain($cid){

        

    	$query = "DELETE FROM cain WHERE CAIN_CODE IN($cid)";

        $this->_db->setQuery($query);

        if($this->_db->query()){

            return true;

        }else{

            return false;

        }

    }

    

     /* pop up */

    function getAllUsertype(){

	   $qr = "SELECT id,title FROM #__usergroups LIMIT 7,3";

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

     

       return $result;

	}

    

      /* get all mail group */ 

   function getAllMailGroup(){

	   $qr = "SELECT MAGR_CODE,MAGR_NAME FROM magr";

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       return $result;

	}

    

     function getAllArea(){

	   $qr = "SELECT AREA_CODE,AREA_NAME FROM area ORDER BY AREA_CODE ASC";

       $this->_db->setQuery($qr);

       $result = $this->_db->loadObjectList();

       return $result;

	}

    

     function getAllPrefOfArea($area_code){

       

       if($area_code == 0){

            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref";

        }else{

            $qr = "SELECT PREF_CODE,PREF_NAME FROM pref WHERE PREF_AREA_CODE=".$area_code;     

        }

        

        $this->_db->setQuery($qr);

        $result = $this->_db->loadObjectList();

       return $result;

	}

    function getAllUserMail($filter){

        $sql_="";

        $w="";

         if($filter['area_search'] != 0){ // neu thanh pho k duoc chon, khu vuc duoc chon thi select tat ca trung tam thuoc khu vuc do

            $w.=" AND PREF_AREA_CODE = ".$filter['area_search'];

        }

        

        if($filter['pref_search'] != 0){  // neu thanh pho duoc chon thi chi select tat ca trung tam thuoc thanh pho do

            $w.=" AND PREF_CODE = ".$filter['pref_search'];

        }

        

        if($filter['ocba_name'] != ''){ // search gan dung voi ten trung tam

            $w.=" AND (OCBA_NAME LIKE '%".$filter['ocba_name']."%' OR OCBA_KANA LIKE '%".$filter['ocba_name']."%')";

        }   

        $sub_select = "(SELECT DISTINCT OCBA_CODE, OCBA_NAME, OCBA_KANA, OCBA_TOWN_CODE, TOWN_PREF_CODE, PREF_CODE, PREF_AREA_CODE

                        FROM ocba

                        JOIN town ON OCBA_TOWN_CODE = TOWN_CODE

                        JOIN pref ON TOWN_PREF_CODE = PREF_CODE

                        WHERE OCBA_FLAG = 1 $w) AS ocba";

        $sql_user = "SELECT id, name, username, email, OCBA_CODE, OCBA_NAME, OCBA_KANA, PREF_CODE, PREF_AREA_CODE, group_id, sendEmail as mail_flag FROM kc2vr_users 

                    JOIN ".$sub_select." ON USEM_OCBA_CODE = OCBA_CODE

                    JOIN kc2vr_user_usergroup_map on id = user_id  ";

                    

       

        switch($filter['usertype_search']){

            case 'all':

                $sql_ = $sql_user." UNION ".$sql_coch;

                break;

            case 'office':

                $sql_ = $sql_user." AND group_id IN (8,9,10) ";

                break;

        

            case 'center':

                $sql_ = $sql_user." AND group_id IN (11,12) AND LEFT(OCBA_CODE,1)='0'";

                break;

            case 'club':

                $sql_ = $sql_user." AND group_id IN (11,12) AND LEFT(OCBA_CODE,1)='1' ";

                break;

        }



        $sql = "SELECT * FROM ($sql_) as result WHERE 1 = 1 " ;           

        

       

        //echo "<p>$sql</p>";   

        $this->_db->setQuery($sql);

        $result = $this->_db->loadObjectList();

        return $result;

    }

   

    

    

}

